package dblayer

import (
	mydb "easycharts/db/mysql"
	"easycharts/model"
	"easycharts/utils/errmsg"
	"fmt"
)

func GetDemoList() ([]model.Echart, int){
	stmt, err := mydb.DBConn().Prepare("select chartId,config,tagId,tag,imgUrl "+ 
		" from echarts_detail")
	if err != nil {
		fmt.Println("Prepare", err.Error())
		return nil, errmsg.ERROR
	}
	defer stmt.Close()

	// 执行查询
	rows, err := stmt.Query()
	if err != nil {
		fmt.Println(err.Error())
		return nil, errmsg.ERROR
	}
	var itemList []model.Echart
	for rows.Next() {
		item := model.Echart{}
		err = rows.Scan(&item.ChartId, &item.Config, &item.TagId, &item.Tag, &item.ImgUrl)
		if err != nil {
			fmt.Println(err.Error())
			break
		}
		itemList = append(itemList, item)
	}
	return itemList, errmsg.SUCCSE
}

func GetTagList() ([]model.Tag, int){
	stmt, err := mydb.DBConn().Prepare("select tagId, detail from " +
		"tag")
	if err != nil {
		fmt.Println(err.Error())
		return nil, errmsg.ERROR
	}
	defer stmt.Close()

	// 执行查询
	rows, err := stmt.Query()
	if err != nil {
		fmt.Println(err.Error())
		return nil, errmsg.ERROR
	}
	var itemList []model.Tag
	for rows.Next() {
		item := model.Tag{}
		err = rows.Scan(&item.TagId, &item.Detail)
		if err != nil {
			fmt.Println(err.Error())
			break
		}
		itemList = append(itemList, item)
	}
	return itemList, errmsg.SUCCSE
}

///////////////////////////////////////

// 查询员工基本信息
func QueryWorker(id int, name string) (model.Worker, int){
	var worker model.Worker
	// 查询员工基本信息
	stmt, err := mydb.DBConn().Prepare("select id, name, sex, role from workers where id = ? and name = ? limit 1")
	if err != nil {
		fmt.Println(err.Error())
		return worker, errmsg.ERROR
	}
	// 函数返回后管理连接
	defer stmt.Close()

	// 执行查询
	err = stmt.QueryRow(id, name).Scan(&worker.Id, &worker.Name, &worker.Sex, &worker.Role)
	if err != nil {
		fmt.Println(err.Error())
		return worker, errmsg.ERROR
	}
	return worker, errmsg.SUCCSE
}

// 获取线路列表
func GetRouteList() ([]model.Route, int){
	// 查询线路的sql语句
	stmt, err := mydb.DBConn().Prepare("select rid, rname from " +
		"routes")
	if err != nil {
		fmt.Println(err.Error())
		return nil, errmsg.ERROR
	}
	defer stmt.Close()

	// 执行查询
	// 根据查询结果填充线路列表数据
	rows, err := stmt.Query()
	if err != nil {
		fmt.Println(err.Error())
		return nil, errmsg.ERROR
	}
	var routeList []model.Route
	for rows.Next() {
		route := model.Route{}
		err = rows.Scan(&route.Rid, &route.Rname)
		if err != nil {
			fmt.Println(err.Error())
			break
		}
		routeList = append(routeList, route)
	}
	return routeList, errmsg.SUCCSE
}

// 获取公司列表
func GetCompanyList() ([]model.Company, int){
	// 查询公司表的基本信息
	stmt, err := mydb.DBConn().Prepare("select cid, cname from " +
		"companys")
	if err != nil {
		fmt.Println(err.Error())
		return nil, errmsg.ERROR
	}
	defer stmt.Close()

	// 根据查询到结果填充公司列表
	rows, err := stmt.Query()
	if err != nil {
		fmt.Println(err.Error())
		return nil, errmsg.ERROR
	}
	var companyList []model.Company
	for rows.Next() {
		company := model.Company{}
		err = rows.Scan(&company.Cid, &company.Name)
		if err != nil {
			fmt.Println(err.Error())
			break
		}
		companyList = append(companyList, company)
	}
	return companyList, errmsg.SUCCSE
}

// 获取车队列表
func GetTeamList() ([]model.TeamInfo, int){
	// 查询所需的sql语句
	stmt, err := mydb.DBConn().Prepare("select tid,cid,cname,lid,lname,sex "+
		"from team_info")
	if err != nil {
		fmt.Println(err.Error())
		return nil, errmsg.ERROR
	}
	defer stmt.Close()

	// 执行查询
	// 填充所需数据
	rows, err := stmt.Query()
	if err != nil {
		fmt.Println(err.Error())
		return nil, errmsg.ERROR
	}
	var teamList []model.TeamInfo
	for rows.Next() {
		teaminfo := model.TeamInfo{}
		err = rows.Scan(&teaminfo.Tid, &teaminfo.Cid, &teaminfo.Cname,
				&teaminfo.Lid, &teaminfo.Lname, &teaminfo.Sex)
		if err != nil {
			fmt.Println(err.Error())
			break
		}
		teamList = append(teamList, teaminfo)
	}
	return teamList, errmsg.SUCCSE
}

// 获取违章类型信息
func GetVioclass() ([]model.Vioclass, int){
	// 查询所需的sql语句
	stmt, err := mydb.DBConn().Prepare("select vtype, score, vname from " +
		"vioclass")
	if err != nil {
		fmt.Println(err.Error())
		return nil, errmsg.ERROR
	}
	defer stmt.Close()

	// 执行查询
	// 填充所需数据
	rows, err := stmt.Query()
	if err != nil {
		fmt.Println(err.Error())
		return nil, errmsg.ERROR
	}
	var vioClasses []model.Vioclass
	for rows.Next() {
		vio := model.Vioclass{}
		err = rows.Scan(&vio.Vtype, &vio.Score, &vio.Vname)
		if err != nil {
			fmt.Println(err.Error())
			break
		}
		vioClasses = append(vioClasses, vio)
	}
	return vioClasses, errmsg.SUCCSE
}

// 查询某个司机的信息
func QueryDriverInfo(id string) (model.TeamMembers, int){
	var driver model.TeamMembers
	// 根据司机编号从team_members视图中获取司机的详细信息
	stmt, err := mydb.DBConn().Prepare("select id,name,sex,tid,rname,role "+
		"from team_members where id = ? limit 1")
	if err != nil {
		fmt.Println(err.Error())
		return driver, errmsg.ERROR
	}
	defer stmt.Close()

	err = stmt.QueryRow(id).Scan(&driver.Id,&driver.Name,&driver.Sex,&driver.Tid,&driver.Rname,&driver.Role)
	if err != nil {
		fmt.Println(err.Error())
		return driver, errmsg.EMPTY
	}
	return driver, errmsg.SUCCSE
}


// 查询某个车队下司机的基本信息
func QueryTeamDrivers(teamId string) ([]model.TeamMembers, int){
	var members []model.TeamMembers
	// 根据车队编号，从车队视图中查询所有车队成员的信息
	stmt, err := mydb.DBConn().Prepare("select id,name,sex,tid,rname,role "+
		"from team_members where tid = ?")
	if err != nil {
		fmt.Println(err.Error())
		return members, errmsg.ERROR
	}
	defer stmt.Close()

	rows, err := stmt.Query(teamId)
	if err != nil {
		fmt.Println(err.Error())
		return nil, errmsg.ERROR
	}

	// 扫描查询结果，插入成员信息列表
	for rows.Next() {
		one := model.TeamMembers{}
		err = rows.Scan(&one.Id,&one.Name,&one.Sex,&one.Tid,&one.Rname,&one.Role)
		// fmt.Println(member)
		if err != nil {
			fmt.Println(err.Error())
			break
		}
		members = append(members, one)
	}

	if err != nil {
		return members, errmsg.ERROR
	}
	if members == nil {
		return members, errmsg.EMPTY
	}
	return members, errmsg.SUCCSE
}


// 查询某名司机在某个时间段的违章详细信息
func QueryDriverViolation(id string, start string, end string) ([]model.VioRecord, int){
	var vioList []model.VioRecord
	// 根据所指定的时间段和司机编号
	// 查询某个司机在某个时段中的违章信息
	stmt, err := mydb.DBConn().Prepare("select vid,id,tid,name,vname,date,site,route " + 
		" from friendlyVios where id=? and date between ? and ?")
	if err != nil {
		fmt.Println(err.Error())
		return vioList, errmsg.ERROR
	}
	defer stmt.Close()

	rows, err := stmt.Query(id, start, end)
	if err != nil {
		fmt.Println(err.Error())
		return nil, errmsg.ERROR
	}
	for rows.Next() {
		vio := model.VioRecord{}
		err = rows.Scan(&vio.Vid, &vio.Id, &vio.Tid,&vio.Name,&vio.Vname,
				&vio.Datetime, &vio.Site, &vio.Rname)
		// fmt.Println(vio)
		if err != nil {
			fmt.Println(err.Error())
			break
		}
		vioList = append(vioList, vio)
	}

	if err != nil {
		return vioList, errmsg.ERROR
	}
	if vioList == nil {
		return vioList, errmsg.EMPTY
	}
	return vioList, errmsg.SUCCSE
}

// 查询某个车队在某个时间段的违章统计信息
func QueryTeamViolation(tid string, start string, end string) ([]model.TeamVios, int){
	var vioList []model.TeamVios
	// 根据车队和违章类型分钟
	// 使用sum函数统计某个时段内，车队的违章信息
	var sql string = "select tid, sum(count), vname from team_violations where tid = ? and date between ? and ? group by tid,vname"
	stmt, err := mydb.DBConn().Prepare(sql)
	if err != nil {
		fmt.Println(err.Error())
		return vioList, errmsg.ERROR
	}
	defer stmt.Close()

	rows, err := stmt.Query(tid, start, end)
	if err != nil {
		fmt.Println(err.Error())
		return nil, errmsg.ERROR
	}
	for rows.Next() {
		vio := model.TeamVios{}
		err = rows.Scan(&vio.Tid, &vio.Cnt, &vio.Vname)
		fmt.Println(vio)
		if err != nil {
			fmt.Println(err.Error())
			break
		}
		vioList = append(vioList, vio)
	}

	if err != nil {
		return vioList, errmsg.ERROR
	}
	if vioList == nil {
		return vioList, errmsg.EMPTY
	}
	return vioList, errmsg.SUCCSE
}



